package com.gc.server.dao.drama;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.gc.model.DramaModel;
import com.gc.server.manager.ConnectionManager;


public class DramaDaoImpl implements DramaDao {

	@Override
	public boolean insertDrama(DramaModel model) {
		Connection connection = ConnectionManager.getConnection();
		try {
			String sql = "INSERT INTO DRAMA(DRAMA_ID,TITLE,GENRE,ACTOR,LAUNCH_DATE,TITLE_PIC,"
					+ "EXPLANATION)values(SEQ_DRAMA_ID.nextval,?,?,?,?,'\\server\\' || SEQ_DRAMA_ID.currval || '.png',?)";
			PreparedStatement pstmt = connection.prepareStatement(sql); 
			pstmt.setString(1, model.getTitle());
			pstmt.setString(2, model.getGenre());
			pstmt.setString(3, model.getActor());
			pstmt.setDate(4, null);
			pstmt.setString(5, model.getExplanation());
			int executeUpdate = pstmt.executeUpdate();
			connection.commit();
			if (executeUpdate == 1) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(connection);
		}
		return false;
	}

	@Override
	public boolean updateDrama(DramaModel model) {
		Connection connection = ConnectionManager.getConnection();
		try {
			String sql = "UPDATE DRAMA SET TITLE = ?, GENRE = ?, ACTOR = ?, "
					+ "EXPLANATION = ? WHERE DRAMA_ID = ?";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, model.getTitle());
			pstmt.setString(2, model.getGenre());
			pstmt.setString(3, model.getActor());
			pstmt.setString(4, model.getExplanation());
			pstmt.setInt(5, model.getDramaId());
			int executeUpdate = pstmt.executeUpdate();
			connection.commit();
			if (executeUpdate == 1) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(connection);
		}
		return false;
	}

	@Override
	public boolean deleteDrama(Integer dramaId) {
		Connection connection = ConnectionManager.getConnection();
		try {
			String sql = "DELETE DRAMA WHERE DRAMA_ID = ?";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, dramaId);
			pstmt.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(connection);
		}
		return false;
	}

	@Override
	public ArrayList<DramaModel> searchDrama(String title) {
		ArrayList<DramaModel> list = new ArrayList<DramaModel>();
		Connection connection = ConnectionManager.getConnection();
		String sql = "SELECT * FROM DRAMA WHERE TITLE = ? ";
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, title);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				int dramaId = rs.getInt("DRAMA_ID");
				String title1 = rs.getString("TITLE");
				String genre = rs.getString("GENRE");
				String actor = rs.getString("ACTOR");
				Date date = rs.getDate("LAUNCH_DATE");
				java.util.Date launchDate = new java.util.Date(date.getTime());
				String titlePic = rs.getString("TITLE_PIC");
				String explanation = rs.getString("EXPLANATION");
				DramaModel dm = new DramaModel(dramaId, title1, genre, actor,
						launchDate, titlePic, explanation);
				list.add(dm);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(connection);
		}
		return list;
	}

	@Override
	public ArrayList<DramaModel> searchDramas() {
		ArrayList<DramaModel> list = new ArrayList<DramaModel>();
		Connection connection = ConnectionManager.getConnection();
		String sql = "SELECT D1.DRAMA_ID DRAMA_ID, TITLE, LAUNCH_DATE, GENRE, ACTOR, TITLE_PIC, EXPLANATION , AVG_RATE "
				+ "FROM DRAMA D1, (SELECT DRAMA_ID , AVG(EVALUATE_RATE) AVG_RATE FROM DRAMA_EVAL_INFO DEI GROUP BY DRAMA_ID) D2 "
				+ "WHERE D1.DRAMA_ID = D2.DRAMA_ID(+)";
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				int dramaId = rs.getInt("DRAMA_ID");
				String title1 = rs.getString("TITLE");
				String genre = rs.getString("GENRE");
				String actor = rs.getString("ACTOR");
				double double1 = rs.getDouble("AVG_RATE");
				//Date date = rs.getDate("LAUNCH_DATE");
				//java.util.Date launchDate = new java.util.Date(date.getTime());
				Date launchDate = null;
//				java.util.Date launchDate = new java.util.Date(date.getTime());
				String titlePic = rs.getString("TITLE_PIC");
				String explanation = rs.getString("EXPLANATION");
				DramaModel dm = new DramaModel(dramaId, title1, genre, actor,
						null, titlePic, explanation);
				dm.setAvgEvaluatedPoint(double1);
				list.add(dm);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(connection);
		}
		return list;
	}

	@Override
	public ArrayList<DramaModel> searchDramas(String userId) {
		ArrayList<DramaModel> list = new ArrayList<DramaModel>();
		Connection connection = ConnectionManager.getConnection();
		String sql = "SELECT D1.DRAMA_ID, TITLE, GENRE, ACTOR, TITLE_PIC, EXPLANATION ,D2.AVG_RATE, (SELECT EVALUATE_RATE FROM DRAMA_EVAL_INFO WHERE DRAMA_ID = D1.DRAMA_ID AND USER_ID = ?) MYAVG"
				+ " FROM DRAMA D1, (SELECT DRAMA_ID , AVG(EVALUATE_RATE) AVG_RATE"
				+ " FROM DRAMA_EVAL_INFO DEI"
				+ " GROUP BY DRAMA_ID) D2"
				+ " WHERE D1.DRAMA_ID = D2.DRAMA_ID(+)";
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, userId);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				int dramaId = rs.getInt("DRAMA_ID");
				String title1 = rs.getString("TITLE");
				String genre = rs.getString("GENRE");
				String actor = rs.getString("ACTOR");
//				Date date = rs.getDate("LAUNCH_DATE");
				double double1 = rs.getDouble("AVG_RATE");
//				java.util.Date launchDate = new java.util.Date(date.getTime());
				String titlePic = rs.getString("TITLE_PIC");
				String explanation = rs.getString("EXPLANATION");
				double double2 = rs.getDouble("MYAVG");
				DramaModel dm = new DramaModel(dramaId, title1, genre, actor,
						null, titlePic, explanation);
				dm.setAvgEvaluatedPoint(double1);
				dm.setMyAvg(double2);
				list.add(dm);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(connection);
		}
		return list;
	}

	@Override
	public DramaModel searchDetailDrama(Integer paramDramaId, String userId) {
		Connection connection = ConnectionManager.getConnection();
		String sql = "SELECT D1.DRAMA_ID, TITLE, GENRE, ACTOR, TITLE_PIC, EXPLANATION ,D2.AVG_RATE, (SELECT EVALUATE_RATE FROM DRAMA_EVAL_INFO WHERE DRAMA_ID = D1.DRAMA_ID AND USER_ID = ?) MYAVG"
				+ " FROM DRAMA D1, (SELECT DRAMA_ID , AVG(EVALUATE_RATE) AVG_RATE"
				+ " FROM DRAMA_EVAL_INFO DEI"
				+ " GROUP BY DRAMA_ID) D2"
				+ " WHERE D1.DRAMA_ID = D2.DRAMA_ID(+)"
				+ " AND D1.DRAMA_ID = ?";
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, userId);
			pstmt.setInt(2, paramDramaId);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				int dramaId = rs.getInt("DRAMA_ID");
				String title1 = rs.getString("TITLE");
				String genre = rs.getString("GENRE");
				String actor = rs.getString("ACTOR");
//				Date date = rs.getDate("LAUNCH_DATE");
				double double1 = rs.getDouble("AVG_RATE");
//				java.util.Date launchDate = new java.util.Date(date.getTime());
				String titlePic = rs.getString("TITLE_PIC");
				String explanation = rs.getString("EXPLANATION");
				double double2 = rs.getDouble("MYAVG");
				DramaModel dm = new DramaModel(dramaId, title1, genre, actor,
						null, titlePic, explanation);
				dm.setAvgEvaluatedPoint(double1);
				dm.setMyAvg(double2);
				return dm;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(connection);
		}
		return null;
	}

	@Override
	public String getDramaFileLocation(Integer dramaId) {
		Connection connection = ConnectionManager.getConnection();
		String sql = "SELECT TITLE_PIC FROM DRAMA WHERE DRAMA_ID = ?";
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, dramaId);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String titlePic = rs.getString(1);
				return titlePic;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(connection);
		}
		return null;
	}

}
